跳到主要内容

MySQL 最左前缀匹配原则

基础概念问题​

1. 什么是联合索引？请详细解释其底层实现原理​

考察点： 索引原理、B+树结构理解

参考答案： 联合索引是多个字段组合建立的索引。底层实现是B+树结构，索引按照字段顺序进行排序。

关键点：

  • 索引字段按从左到右的顺序构建B+树
  • 第一个字段全局有序，后续字段在前一字段相等时有序
  • 实际等价于创建了多个索引：(id)、(id,name)、(id,name,age)

2. 什么是最左前缀匹配原则？为什么会有这个限制？​

考察点： 索引使用规则、查询优化理解

参考答案： 最左前缀匹配原则是指查询时必须从联合索引的最左边字段开始，连续匹配索引字段才能使用索引。

原因： B+树的构建顺序决定了只有最左边的字段是全局有序的，其他字段只在前面字段相等的情况下才有序。

实际场景问题​

3. 给定一个联合索引(a,b,c)，以下SQL哪些能用到索引？请分析原因​

-- SQL1: SELECT * FROM table WHERE a = 1;
-- SQL2: SELECT * FROM table WHERE b = 2;  
-- SQL3: SELECT * FROM table WHERE a = 1 AND c = 3;
-- SQL4: SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;
-- SQL5: SELECT * FROM table WHERE b = 2 AND a = 1;

考察点： 最左前缀原则应用、查询优化器理解

参考答案：

  • SQL1: ✅ 能用到索引(a)
  • SQL2: ❌ 不能用到索引，违反最左前缀原则
  • SQL3: ✅ 能用到索引(a)，但c字段无法使用索引
  • SQL4: ✅ 能用到完整索引(a,b,c)
  • SQL5: ✅ 能用到完整索引，查询优化器会调整字段顺序

4. EXPLAIN的type字段有哪些值？性能排序如何？​

考察点： 查询分析、性能调优

参考答案（按性能从高到低）：

复杂场景分析​

5. 请分析以下查询的执行过程，并画出时序图​

场景： 表staffs有联合索引(id,name,age)，执行查询：

SELECT * FROM staffs WHERE name = 'John' AND age = 25;

解释说明：

  1. 查询分析阶段：优化器发现查询条件不满足最左前缀原则
  2. 执行计划选择：选择index类型，对整个联合索引进行扫描
  3. 索引扫描：从索引树第一个节点开始逐一检查
  4. 条件匹配：对每个索引项检查name和age是否匹配
  5. 回表操作：匹配的记录需要回表获取完整数据
  6. 性能影响：比全表扫描快（索引更小），但比正确使用索引慢很多

6. 范围查询对联合索引的影响，请分析并绘制决策流程图​

场景分析： 联合索引(a,b,c)的范围查询处理

7. 请设计一个复杂场景：电商订单查询优化​

场景描述： 订单表orders有以下常见查询：

  1. 按用户ID查询订单
  2. 按时间范围查询
  3. 按状态查询
  4. 组合查询：用户ID + 时间范围 + 状态

问题： 如何设计索引？请分析查询执行流程。

索引设计建议：

-- 主要索引：支持最常见的组合查询
CREATE INDEX idx_user_time_status ON orders(user_id, create_time, status);

-- 备选索引：支持纯状态查询  
CREATE INDEX idx_status_time ON orders(status, create_time);

-- 考虑字段区分度：user_id > create_time > status

性能调优问题​

8. 如何判断联合索引字段的最佳排序？​

考察点： 索引设计原则、性能优化

参考答案：

判断标准优先级：

  1. 查询频率：最常用的查询条件放在最左边
  2. 字段区分度：SELECT COUNT(DISTINCT column)/COUNT(*) FROM table
  3. 范围查询影响：范围查询字段尽量放在后面
-- 分析字段区分度
SELECT 
    COUNT(DISTINCT user_id)/COUNT(*) as user_selectivity,
    COUNT(DISTINCT status)/COUNT(*) as status_selectivity,  
    COUNT(DISTINCT create_time)/COUNT(*) as time_selectivity
FROM orders;

9. 前缀索引的应用场景和实现方法​

考察点： 索引优化技巧

应用场景： 长字符串字段（如邮箱、URL、长文本等）

实现步骤：

-- 1. 分析前缀长度的区分度
SELECT 
    COUNT(DISTINCT LEFT(email, 5))/COUNT(*) as prefix_5,
    COUNT(DISTINCT LEFT(email, 10))/COUNT(*) as prefix_10,
    COUNT(DISTINCT LEFT(email, 15))/COUNT(*) as prefix_15
FROM users;

-- 2. 选择合适长度创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

总结​

掌握MySQL索引优化需要理解：

  1. B+树存储结构 决定了最左前缀原则
  2. 查询优化器 会自动调整简单的字段顺序
  3. 范围查询 会阻断后续字段的索引使用
  4. EXPLAIN分析 是调优的重要工具
  5. 索引设计 需要结合实际业务场景

References​